SQL Indexes

SQL query works fast on a small data sets or table with less number of rows, but in the real world database table contains millions of rows and hundreds of tables and we if try to apply a search query on a table which contains millions of rows then the SQL engine would take a lot of time to fetch the data from the table. For instance, if you perform a COUNT query on a table with 200 million rows then it would probably take 4 to 5 seconds to show the result, 4 to 5 seconds might not seem a heck of a deal but here we are talking about a single query, what if we try to perform a JOIN operation or some other query which has more than one statements there the SQL engine would easily take 20 to 30 seconds.

SQL Indexes

To resolve this problem we use SQL INDEX. Indexes in SQL are used to speed up the SQL queries on database tables, the concept of SQL INDEX is similar to the index of a book. For instance, if you want to learn about the Algebra you would not flip through your entire math book page by page scanning for the term Algebra, instead, you will go to the index page and look for the list of pages and there you search for the Algebra, and this similar technique is used by the SQL database itself. When we create an Index, the database will generate a method to find the data based on one or more than one column.

When Should we Create index?

• When there is a lot of records in the database table
• When columns do not contain too many NULL values
• When there are complex queries performing on the database.

Always try to avoid index if there are less number of records in the database because INDEX occupies space in ht memory and slow down the process of INSERT and CREATE query.

CREATE INDEX Syntax

INDEX is used on tables columns so we can create indexes once there is a Table.

CREATE INDEX index_name
ON table_name (column_name_1, column_2_name);

Example For the example consider this table Students

+------+--------+------+--------+-------+----------+
| id   | name   | age  | grades | marks | Trade    |
+------+--------+------+--------+-------+----------+
|    1 | Luffy  |   16 | A      |   970 | Science  |
|    2 | Naruto |   18 | A      |   960 | Humanity |
|    3 | Zoro   |   20 | A      |   940 | Commerce |
|    4 | Sanji  |   21 | B      |   899 | Humanity |
|    5 | Nami   |   17 | B      |   896 | Science  |
|    6 | Robin  | NULL | B      |   860 | Humanity |
|    7 | Goku   |   27 | B      |   860 | Humanity |
+------+--------+------+--------+-------+----------+

<Note> There is no use of creating Indexes for this small table, there would be no speed change in data retrieval. Query: Create an Index "in_name"on the column "name" in the "Students" table

CREATE INDEX in_name
ON Students(name);

Types of Index

There is mainly 3 types of INDEX:

• Single Index
• Unique Index
• Composite Index

1. Single Index

If we create an Index for Single only one table column, then it would be a Single INDEX. Syntax:

CREATE INDEX index_name
ON table_name (column_name);

2. Unique Index

Unique Index used in that column where we want no duplicate values. Syntax:

CREATE UNIQUE INDEX index_name
ON table_name (column_name);

3. Composite Index

In the Composite index, we create indexes for more than one column. Syntax

CREATE INDEX index_name
ON table_name (column_name_1, column_name_2);

Deleting the INDEX

When we do not require index for a column then using the DROP command we can delete the column INDEX. Syntax The Syntax for dropping the INDEX vary from RDBMS to RDBMS. MS Access

DROP INDEX index_name ON table_name;

SQL Server

DROP INDEX index_name;

Oracle

DROP INDEX table_name.index_name;

MySQL

ALTER TABLE table_name
DROP INDEX index_name;